﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Formsy.Data.Interfaces;
using System.Data;
using System.IO;
using System.Web;
using System.Data.Linq;
using Formsy.Data.Linq;
using Anthony.Library;
using Formsy.Common;
using System.Xml.Linq;
using Microsoft.Practices.EnterpriseLibrary.PolicyInjection.CallHandlers;
using System.Xml.Serialization;
//using Microsoft.Practices.EnterpriseLibrary.PolicyInjection;

namespace Formsy.Data.Services
{
    public class FormsyService : IFormsyService
    {
        protected FormsyDataContext m_Db;



        #region FormsyService

        public FormsyService(FormsyDataContext db)
        {
            m_Db = db;
        }

        #endregion



        #region LoadXDocument

        /// <summary>
        /// Loads Xml file and caches for 30 minutes.
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        [CachingCallHandler(0, 30, 0)]
        public XDocument LoadXDocument(string path)
        {
            return XDocument.Load(HttpContext.Current.Server.MapPath(path));
        }

        #endregion



        #region InsertEntry

        /// <summary>
        /// Inserts and saves an entry into the main entry table.
        /// </summary>
        /// <param name="formName"></param>
        /// <returns></returns>
        public IFormsyEntry InsertEntry(string formName)
        {
            // Inserts form into main form table if it does not already exist
            EnsureFormExists(formName);

            int formId = GetForm(formName).Id;

            // Create PromotionEntry
            FormsyEntry entry = new Formsy.Data.Linq.FormsyEntry(formId);
            m_Db.FormsyEntries.InsertOnSubmit(entry);

            m_Db.SubmitChanges();

            return entry;
        }

        #endregion



        #region InsertEntryData

        /// <summary>
        /// Inserts and saves an entry in the name-value pair table.
        /// </summary>
        /// <param name="entryId"></param>
        /// <param name="dictionary"></param>
        /// <returns></returns>
        public List<IFormsyEntryData> InsertEntryData(int formId, int entryId, Dictionary<string, string> dictionary)
        {
            List<IFormsyEntryData> dataItems = new List<IFormsyEntryData>();

            // iterate through dictionary and create entry data pairs
            foreach (var v in dictionary)
            {
                // create new
                FormsyEntryData entryData = new FormsyEntryData(entryId, v.Key, v.Value);
                m_Db.FormsyEntryDatas.InsertOnSubmit(entryData);

                // save to collection
                dataItems.Add(entryData);
            }

            // update columns
            EnsureColumnNames(formId, dictionary);

            // save
            m_Db.SubmitChanges();

            // add the dataItems as serialized xml
            //var items = dataItems.Cast<FormsyEntryData>().ToList();
            AddXmlToEntry(entryId, dataItems.Cast<FormsyEntryData>().ToList());

            return dataItems;
        }

        #endregion



        #region EnsureColumnNames


        /// <summary>
        /// Inserts new field names in form table.
        /// </summary>
        /// <param name="dictionary"></param>
        private void EnsureColumnNames(int formId, Dictionary<string, string> dictionary)
        {
            // get field names from form xml
            var form = (from f in m_Db.FormsyForms
                        where f.Id == formId
                        select f).Single();
            
            // get field names from db
            List<string> dbFieldNames;
            if (!string.IsNullOrEmpty(form.Fields))
                dbFieldNames = form.Fields.Split(",".ToCharArray()).ToList();
            else
                dbFieldNames = new List<string>();

            // get field names from dictionary
            List<string> fieldNames = dictionary.Keys.ToList();

            // add field names from dictionary that do not already exist in db
            foreach (var name in fieldNames)
            {
                if (!dbFieldNames.Contains(name))
                    dbFieldNames.Add(name);
            }

            // update db
            form.Fields = string.Join(",", dbFieldNames.ToArray());
        }
        
        #endregion



        #region AddXmlToEntry

        /// <summary>
        /// Serializes dataItems to Xml, then inserts into Entry table.
        /// </summary>
        /// <param name="entryId"></param>
        /// <param name="dataItems"></param>
        protected void AddXmlToEntry(int entryId, List<FormsyEntryData> dataItems)
        {
            // serialize dataItems into xml string
            string serializedData = XmlSerializing.ToXml(dataItems, typeof(List<FormsyEntryData>));

            // get the entry
            FormsyEntry entry = (FormsyEntry)GetEntry(entryId);

            // set xml
            entry.Xml = serializedData;
            entry.Updated = DateTime.Now;

            m_Db.SubmitChanges();
        }

        #endregion



        #region GetEntry

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entryId"></param>
        /// <returns>The entry with the given Id. Null if it does not exist.</returns>
        public IFormsyEntry GetEntry(int entryId)
        {
            return (from e in m_Db.FormsyEntries
                    where e.Id == entryId
                    select e).FirstOrDefault();
        }

        #endregion



        #region EnsureFormExists

        /// <summary>
        /// Checks that form exists. Creates form if it does not exist.
        /// </summary>
        /// <param name="formName"></param>
        public IFormsyForm EnsureFormExists(string formName)
        {
            IFormsyForm form = GetForm(formName);

            if (form == null)
                form = CreateForm(formName);

            return form;
        }

        #endregion



        #region CreateForm

        /// <summary>
        /// Inserts and saves new form.
        /// </summary>
        /// <param name="formName"></param>
        /// <returns></returns>
        public IFormsyForm CreateForm(string formName)
        {
            FormsyForm form = new FormsyForm(formName);
            form.Fields = "";
            form.Created = DateTime.Now;
            m_Db.FormsyForms.InsertOnSubmit(form);
            m_Db.SubmitChanges();
            return form;
        }


        #endregion



        #region UpdateEntryData

        public List<IFormsyEntryData> UpdateEntryData(int entryId, Dictionary<string, string> dictionary)
        {
            var entry = GetEntry(entryId);

            return UpdateEntryData(entry.FormId, entryId, dictionary);
        }


        public List<IFormsyEntryData> UpdateEntryData(int formId, int entryId, Dictionary<string, string> dictionary)
        {
            // get all data items for this entry
            var datas = GetEntryDatas(entryId);

            // iterate through dictionary, set data value where data name == dictionary key
            foreach (var key in dictionary.Keys)
            {
                var data = datas.Where(x => x.Name == key).SingleOrDefault();
                if (data != null)
                    data.Value = dictionary[key];
            }

            // add any new field names to database
            EnsureColumnNames(formId, dictionary);

            m_Db.SubmitChanges();

            // update entry with new xml
            AddXmlToEntry(entryId, datas.Cast<FormsyEntryData>().ToList());

            return datas;
        }

        #endregion



        #region DeleteEntryAndData

        public bool DeleteEntryAndData(int entryId)
        {
            // get and delete entry
            var entry = GetEntry(entryId);
            m_Db.FormsyEntries.DeleteOnSubmit((FormsyEntry)entry);

            // get and delete entry datas
            var datas = GetEntryDatas(entryId);
            datas.ForEach(x => m_Db.FormsyEntryDatas.DeleteOnSubmit((FormsyEntryData)x));

            try
            {
                m_Db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        
        #endregion



        #region GetForm

        /// <summary>
        /// 
        /// </summary>
        /// <param name="formName"></param>
        /// <returns>The Id of a form that matches formName. There can be only one!</returns>
        public IFormsyForm GetForm(string formName)
        {
            return (from c in m_Db.FormsyForms
                    where c.Name.ToLower() == formName.ToLower()
                    select c).FirstOrDefault();
        }

        #endregion




        #region GetEntryDatasFromXml

        /// <summary>
        /// Deserializes xml string and returns a list of list of IFormsyEntryData.
        /// </summary>
        /// <param name="xml"></param>
        /// <returns></returns>
        protected List<IFormsyEntryData> GetEntryDatasFromXml(string xml)
        {
            List<FormsyEntryData> list = (List<FormsyEntryData>)XmlSerializing.FromXml(xml, typeof(List<FormsyEntryData>));
            return list.Cast<IFormsyEntryData>().ToList();
        }

        #endregion



        #region GetEntries

        /// <summary>
        /// 
        /// </summary>
        /// <param name="formName"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns>A list of entries between the specified dates.</returns>
        public List<IFormsyEntry> GetEntries(string formName, DateTime start, DateTime end)
        {
            var query = (from c in m_Db.FormsyEntries
                         where c.Created.Date >= start.Date && c.Created.Date < end.Date
                         && c.FormId == GetForm(formName).Id
                         select c);
            return query.Cast<IFormsyEntry>().ToList();
        }


        #endregion



        #region GetEntryDatas

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entryId"></param>
        /// <returns>A list of entry data's</returns>
        public List<IFormsyEntryData> GetEntryDatas(int entryId)
        {
            var query = (from e in m_Db.FormsyEntryDatas
                         where e.EntryId == entryId
                         select e);
            return query.Cast<IFormsyEntryData>().ToList();
        }

        #endregion




        #region GetCSV

        /// <summary>
        /// Generic method to get the data.
        /// 
        /// There is a chance that the fields collected may change during the form's life. 
        /// We need to use a list of headers, check against the data.name, and insert null if the data doesnt exist.
        /// </summary>
        /// <param name="formName"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="path"></param>
        /// <param name="ignoreList"></param>
        /// <returns></returns>
        public DataTable GetCSV(string formName, DateTime start, DateTime end, string path, List<string> ignoreList)
        {
            DataTable table = new DataTable();
            var entries = GetEntries(formName, start, end);

            // get the headings
            List<string> mainHeadings = new List<string>();
            DataRow headingsRow = table.NewRow();
            table = GetHeadings(formName, mainHeadings, table, ignoreList);

            // for each entry, load values
            foreach (IFormsyEntry e in entries)
            {
                var entryDataItems = GetEntryDatas(e.Id);

                // construct the row with the current entry, and entry data
                DataRow row = table.NewRow();
                List<string> items = GetValuesForTable(table.Columns, mainHeadings, e, entryDataItems);
                row.ItemArray = items.ToArray();
                table.Rows.Add(row);
            }
            return table;
        }


        #endregion




        #region GetHeadings

        /// <summary>
        /// Gets the headings for the table we will create.
        /// FormsyEntry has predefined column names (Id, Created and Updated). The name value pairs will be dynamic.
        /// </summary>
        /// <param name="entries"></param>
        /// <param name="headings"></param>
        /// <param name="table"></param>
        /// <param name="excludeList"></param>
        /// <returns></returns>
        protected DataTable GetHeadings(string formName, List<string> headings, DataTable table, List<string> excludeList)
        {
            // add entry columns to headings
            headings.Add("Id");
            headings.Add("Created");
            headings.Add("Updated");

            // add entry columns to table
            table.Columns.Add("Id");
            table.Columns.Add("Created");
            table.Columns.Add("Updated");

            //  get form.Fields 
            string[] columns = GetForm(formName)
                                    .Fields
                                    .Split(",".ToCharArray());

            // add headings to table
            foreach (string name in columns)
            {
                if (!excludeList.Contains(name))
                    table.Columns.Add(name);
            }

            return table;
        }



        /// <summary>
        /// Creates a list from the field names.
        /// FormsyEntry has predefined column names (Id, Created and Updated). The name value pairs will be dynamic.
        /// </summary>
        /// <param name="formName"></param>
        /// <param name="ignoreList"></param>
        /// <returns></returns>
        public List<string> GetHeadings(string formName, List<string> ignoreList)
        {
            List<string> headings = new List<string>();

            // add entry columns to headings
            headings.Add("Id");
            headings.Add("Created");
            headings.Add("Updated");
            
            //  get form.Fields 
            string[] columns = GetForm(formName)
                                    .Fields
                                    .Split(",".ToCharArray());

            // add headings to table
            foreach (string name in columns)
            {
                if (!ignoreList.Contains(name))
                    headings.Add(name);
            }

            return headings;
        }

        #endregion



        #region LoadItemsForCSV

        /// <summary>
        /// Used by GetCSV()
        /// The generic CSV extract assumes only a single uploaded file. ie File will be {EntryID}.{extension}. 
        /// </summary>
        /// <param name="colHeadings"></param>
        /// <param name="entry"></param>
        /// <param name="data"></param>
        /// <param name="mainHeadings"></param>
        /// <param name="code"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        protected List<string> GetValuesForTable(DataColumnCollection colHeadings, List<string> mainHeadings, IFormsyEntry entry, List<IFormsyEntryData> data)
        {
            List<string> items = new List<string>();
            items.Add(entry.Id.ToString());
            items.Add(entry.Created.ToString());
            items.Add(entry.Updated.ToString());

            // there is a chance that the fields collected may change during the form's life. 
            // here we need to check against the data.name, and insert null if the data doesnt exist.
            foreach (DataColumn col in colHeadings)
            {
                if (!mainHeadings.Contains(col.ColumnName)) 
                {
                    // get entry data with name == column name
                    IFormsyEntryData d = data.Where(x => x.Name == col.ColumnName).FirstOrDefault();

                    if (d == null)
                        items.Add(string.Empty); // add empty string to signify no data
                    else
                        items.Add(d.Value);
                }
            }

            return items;
        }

        #endregion


    }
}
